package com.hulu.yec.sys.yecclass.service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.hulu.yec.sys.yecclass.entity.ClassEntity;

public class TermDao {

	@SuppressWarnings("unused")
	public static List<ClassEntity> IfList(String coursename,
			String createtimeup, String createtimedown) throws SQLException {
		// 条件判断
		String sql = null;
		if (coursename == "" && createtimeup == "" && createtimedown == "") {
			sql = "SELECT c.id, c.name, p.name as 'course_name', c.operate_time, c.state, c.memo FROM sys_class c, sys_course p WHERE p.id=c.course_id ORDER BY c.id DESC";

		} else if (coursename != "" && createtimeup == ""
				&& createtimedown == "") {
			sql = "SELECT c.id, c.name, p.name as 'course_name', c.operate_time, c.state, c.memo FROM sys_class c, sys_course p WHERE p.id=c.course_id AND p.name='"
					+ coursename + "' ";

		} else if (coursename == "" && createtimeup != ""
				&& createtimedown == "") {
			sql = "SELECT c.id, c.name, p.name as 'course_name', c.operate_time, c.state, c.memo FROM sys_class c, sys_course p WHERE p.id=c.course_id AND  c.operate_time='"
					+ createtimeup + "'";
		} else if (coursename == "" && createtimeup == ""
				&& createtimedown != "") {
			sql = "SELECT c.id, c.name, p.name as 'course_name', c.operate_time, c.state, c.memo FROM sys_class c, sys_course p WHERE p.id=c.course_id AND  c.operate_time='"
					+ createtimedown + "'";

		} else if (coursename != "" && createtimeup != ""
				&& createtimedown == "") {
			sql = "SELECT c.id, c.name, p.name as 'course_name', c.operate_time, c.state, c.memo FROM sys_class c, sys_course p WHERE p.id=c.course_id AND p.name='"
					+ coursename
					+ "' AND c.operate_time='"
					+ createtimeup
					+ "'";
		} else if (coursename != "" && createtimeup == ""
				&& createtimedown != "") {
			sql = "SELECT c.id, c.name, p.name as 'course_name', c.operate_time, c.state, c.memo FROM sys_class c, sys_course p WHERE p.id=c.course_id AND p.name='"
					+ coursename
					+ "' AND  c.operate_time='"
					+ createtimedown
					+ "'";

		} else if (coursename == "" && createtimeup != ""
				&& createtimedown != "") {
			sql = "SELECT c.id, c.name, p.name as 'course_name', c.operate_time, c.state, c.memo FROM sys_class c, sys_course p WHERE p.id=c.course_id AND '"
					+ createtimeup
					+ "'<=c.operate_time AND c.operate_time<='"
					+ createtimedown + "';";

		} else if (coursename != "" && createtimeup != ""
				&& createtimedown != "") {
			sql = "SELECT c.id, c.name, p.name as 'course_name', c.operate_time, c.state, c.memo FROM sys_class c, sys_course p WHERE p.id=c.course_id AND p.name='"
					+ coursename
					+ "' AND '"
					+ createtimeup
					+ "'<=c.operate_time AND c.operate_time <='"
					+ createtimedown + "';";
		}

		List<ClassEntity> listUser = new ArrayList<>();

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		try {
			Class.forName("com.mysql.jdbc.Driver");
			connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/exam", "root", "root");
			preparedStatement = connection.prepareStatement(sql);

			resultSet = preparedStatement.executeQuery();

			while (resultSet.next()) {
				ClassEntity listEntity = new ClassEntity();

				listEntity.setId(resultSet.getInt("id"));
				listEntity.setName(resultSet.getString("name"));
				listEntity.setCourse_name(resultSet.getString("course_name"));
				listEntity.setOperateTime(resultSet
						.getTimestamp("operate_time"));
				listEntity.setState(resultSet.getShort("state"));
				listEntity.setMemo(resultSet.getString("memo"));
				listUser.add(listEntity);
			}
			return listUser;

		} catch (ClassNotFoundException | SQLException e) {

			e.printStackTrace();
			throw new RuntimeException("失败~");
		} finally {
			// 4、资源关闭

			if (resultSet != null) {
				resultSet.close();
			}
			if (preparedStatement != null) {
				preparedStatement.close();
			}
			if (connection != null) {
				connection.close();
			}

		}
	}
}
